package org.firecloud.h5fun.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.firecloud.h5fun.model.javabean.ApplicationBean;
import org.firecloud.h5fun.model.javabean.ColumnBean;
import org.firecloud.h5fun.model.javabean.CommentaryBean;
import org.firecloud.h5fun.model.javabean.ProjectBean;
import org.firecloud.h5fun.model.javabean.SearchQueryBean;
import org.firecloud.h5fun.model.javabean.UserBean;


/**
 * 通用查询类，负责数据库查询操作
 * @author fc
 */
public class CommonQuery {

	/**
	 * 通过查询columns表返回集构造 {@link ColumnBean}
	 * @param colum 要构造的 {@link ColumnBean}
	 * @param resultSet 查询columns表返回集
	 * @throws Exception 返回集错误
	 */
	private final static void set_column(ColumnBean colum, ResultSet resultSet) throws Exception {
		colum.setId(resultSet.getByte("columnId"));
		colum.setName(resultSet.getString("columnName"));
		colum.setPriority(resultSet.getInt("columnPriority"));
	}

	/**
	 * 通过查询projects表返回集构造 {@link ProjectBean}
	 * @param project 要构造的 {@link ProjectBean}
	 * @param resultSet 查询projects表返回集
	 * @throws Exception 返回集错误
	 */
	private final static void set_project(ProjectBean project, ResultSet resultSet) throws Exception {
		project.setId(resultSet.getInt("projectId"));
		project.setClick(resultSet.getInt("projectClick"));
		project.setCommentary(resultSet.getInt("projectCommentary"));
		project.setColumnId(resultSet.getByte("projectColumnId"));
		project.setOriginal(resultSet.getBoolean("projectIsOriginal"));
		project.setTitle(resultSet.getString("projectTitle"));
		project.setImageFileName(resultSet.getString("projectImageFileName"));
		project.setContext(resultSet.getString("projectContext"));
		project.setIframeUrl(resultSet.getString("projectIframeUrl"));
		project.setOriginalUrl(resultSet.getString("projectOriginalUrl"));
		project.setCreateTime(resultSet.getTimestamp("projectCreateTime"));
		project.setCreateApp(resultSet.getBoolean("projectIsCreateApp"));
	}
	
	/**
	 * 通过查询users表返回集构造 {@link UserBean}
	 * @param user 要构造的 {@link UserBean}
	 * @param resultSet 查询users表返回集
	 * @throws Exception 返回集错误
	 */
	private final static void set_user(UserBean user, ResultSet resultSet) throws Exception {
		user.setId(resultSet.getInt("userId"));
		user.setName(resultSet.getString("userName"));
		user.setMail(resultSet.getString("userMail"));
		user.setPassWordMd5(resultSet.getString("userPassWordMd5"));
		user.setScore(resultSet.getInt("userScore"));
		user.setRegTime(resultSet.getTimestamp("userRegTime"));
		user.setLastLoginTime(resultSet.getTimestamp("userLastLoginTime"));
		user.setAdmin(resultSet.getBoolean("userIsAdmin"));
	}

	/**
	 * 通过查询apps表返回集构造 {@link ApplicationBean}
	 * @param app 要构造的 {@link ApplicationBean}
	 * @param resultSet 查询apps表返回集
	 * @throws Exception 返回集错误
	 */
	private final static void set_app(ApplicationBean app, ResultSet resultSet) throws Exception {
		app.setId(resultSet.getInt("appId"));
		app.setProjectId(resultSet.getInt("appProjectId"));
		app.setUserId(resultSet.getInt("appUserId"));
		app.setPublic(resultSet.getBoolean("appIsPublic"));
		app.setName(resultSet.getString("appName"));
		app.setContext(resultSet.getString("appContext"));
		app.setData(resultSet.getString("appData"));
		app.setClick(resultSet.getInt("appClick"));
		app.setCommentary(resultSet.getInt("appCommentary"));
		app.setCreateTime(resultSet.getTimestamp("appCreateTime"));
	}
	
	/**
	 * 通过查询commentarys表返回集构造 {@link CommentaryBean}
	 * @param commentary 要构造的 {@link CommentaryBean}
	 * @param resultSet 查询apps表返回集
	 * @throws Exception 返回集错误
	 */
	private final static void set_commentary(CommentaryBean commentary, ResultSet resultSet) throws Exception {
		commentary.setId(resultSet.getInt("commentaryId"));
		commentary.setUserId(resultSet.getInt("commentaryUserId"));
		commentary.setProjectId(resultSet.getInt("commentaryProjectId"));
		commentary.setAppId(resultSet.getInt("commentaryAppId"));
		commentary.setContext(resultSet.getString("commentaryContext"));
		commentary.setCreateTime(resultSet.getTimestamp("commentaryCreateTime"));
	}
	
	/**
	 * 查询出Colums表所有内容构造出 {@link ColumnBean} 列表对象
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param colums 造出的 {@link ColumnBean} 列表对象
	 * @throws Exception 返回集错误
	 */
	public final static void select_columns(Connection conn, List<ColumnBean> colums) throws Exception {
		ColumnBean colum = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM columns ORDER BY columnPriority;");
			while (resultSet.next()) {
				colum = new ColumnBean();
				set_column(colum, resultSet);
				colums.add(colum);
			}
		} finally {
			if (statement != null) {
				statement.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
		}
	}

	/**
	 * 通过UserID查询出{@link UserBean}
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 用户ID
	 * @return 查询出的{@link UserBean}
	 * @throws Exception 返回集错误
	 */
	public final static UserBean select_user(Connection conn, int id) throws Exception {
		UserBean user = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = conn.prepareStatement("SELECT * FROM users WHERE userId=?;");
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				user = new UserBean();
				set_user(user, resultSet);
				resultSet.close();
			} else {
				resultSet.close();
				throw new CommonQueryInfoError();
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
		}
		return user;
	}
	
	/**
	 * 更新用户最后登陆时间
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 用户ID
	 * @throws Exception 用户不存在
	 */
	public final static void update_user_last_login_time(Connection conn, int id) throws Exception {
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = conn.prepareStatement("UPDATE users SET userLastLoginTime=CURRENT_TIMESTAMP WHERE userId=?;");
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
		}finally{
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}
	
	/**
	 * 查询用户登陆
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param mail 用户邮箱
	 * @param password 用户密码
	 * @return 对应用户对象
	 * @throws Exception 没有用户或数据错误
	 */
	public final static UserBean select_login(Connection conn, String mail, String password) throws Exception {
		UserBean user = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = conn
					.prepareStatement("SELECT * FROM users WHERE userMail=? AND userPassWordMd5=MD5(?);");
			preparedStatement.setString(1, mail);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				user = new UserBean();
				set_user(user, resultSet);
				resultSet.close();
			} else {
				resultSet.close();
				throw new CommonQueryInfoError();
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
		}
		return user;
	}

	/**
	 * 通过ID查询Project的信息
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id Project的ID
	 * @return 查询出的Project对象
	 * @throws Exception ID不存在
	 */
	public final static ProjectBean select_project(Connection conn, int id) throws Exception {
		ProjectBean project = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = conn.prepareStatement("SELECT * FROM projects WHERE projectId=?;");
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				project = new ProjectBean();
				set_project(project, resultSet);
			} else {
				throw new CommonQueryInfoError();
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
		}
		return project;
	}
	
	/**
	 * Project点击数加1
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 加1的ProjectID
	 * @throws Exception ID不存在
	 */
	public final static void update_project_inc_click(Connection conn, int id) throws Exception {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement("UPDATE projects SET projectClick=projectClick+1 WHERE projectId=?;");
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}
	
	/**
	 * App点击数加1
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 加1的AppID
	 * @throws Exception ID不存在
	 */
	public final static void update_app_inc_click(Connection conn, int id) throws Exception {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement("UPDATE apps SET appClick=appClick+1 WHERE appId=?;");
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}

	/**
	 * 注册时插入用户
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param mail 邮箱
	 * @param name 名字
	 * @param password 密码
	 * @throws SQLException 数据库错误
	 * @throws CommonQueryMailUsedError 邮箱已被注册
	 * @throws Exception 其他错误
	 */
	public final static void insert_register(Connection conn, String mail, String name, String password)
			throws SQLException, CommonQueryMailUsedError, Exception {
		PreparedStatement psMail = null;
		ResultSet resultSet = null;
		PreparedStatement psInsert = null;
		try {
			psMail = conn.prepareStatement("SELECT userId FROM users WHERE userMail=?;");
			psMail.setString(1, mail);
			resultSet = psMail.executeQuery();
			int updaterow = 0;
			if (resultSet.next()) {
				throw new CommonQueryMailUsedError();
			} else {
				psInsert = conn
						.prepareStatement("INSERT INTO users(userName,userMail,userPassWordMd5) VALUES (?,?,MD5(?));");
				psInsert.setString(1, name);
				psInsert.setString(2, mail);
				psInsert.setString(3, password);
				updaterow = psInsert.executeUpdate();
				if (updaterow != 1) {
					throw new Exception("Update rows number not is one!");
				}
			}
		} finally {
			if (psMail != null) {
				psMail.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
			if (psInsert != null) {
				psInsert.close();
			}
		}
	}

	/**
	 * 插入Project
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param columns_id 栏目ID
	 * @param isoriginal 是否原创
	 * @param title 标题
	 * @param image_filename 封面图片名
	 * @param context 简介内容
	 * @param iframe_url 框架URL或者Apps目录名
	 * @param original_url 转载内容原URL
	 * @param isCreateApp 是否可定制生成App
	 * @throws SQLException 数据库错误
	 * @throws Exception 其他错误
	 */
	public final static void insert_project(Connection conn, int columns_id, boolean isoriginal, String title,
			String image_filename, String context, String iframe_url, String original_url, boolean isCreateApp)
					throws SQLException, Exception {
		PreparedStatement psInsert = null;
		try {
			psInsert = conn.prepareStatement(
					"INSERT INTO projects(projectColumnId,projectIsOriginal,projectTitle,projectImageFileName,projectContext,projectIframeUrl,projectOriginalUrl,projectIsCreateApp) VALUES (?,?,?,?,?,?,?,?);");
			psInsert.setInt(1, columns_id);
			psInsert.setBoolean(2, isoriginal);
			psInsert.setString(3, title);
			psInsert.setString(4, image_filename);
			psInsert.setString(5, context);
			psInsert.setString(6, iframe_url);
			psInsert.setString(7, original_url);
			psInsert.setBoolean(8, isCreateApp);
			int updaterow = psInsert.executeUpdate();
			if (updaterow != 1) {
				throw new Exception("Update rows number not is one!");
			}
		} finally {
			if (psInsert != null) {
				psInsert.close();
			}
		}
	}

	/**
	 * 条件查询Projects
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param list 构造的Project返回列表
	 * @param queryBean 查询条件 {@link SearchQueryBean}
	 * @param size 每页大小
	 * @param page 页数
	 * @return 根据查询条件的返回记录数
	 * @throws Exception 数据错误
	 */
	public final static int select_projects(Connection conn, List<ProjectBean> list, SearchQueryBean queryBean,
			int size, int page) throws Exception {
		int sum = 0;
		PreparedStatement psProjects = null;
		PreparedStatement psCount = null;
		ResultSet resultSetCount = null;
		ResultSet resultSet = null;
		try {
			psProjects = conn.prepareStatement(
					"SELECT * FROM projects WHERE (?=0 OR projectColumnId=?) AND (? IS NULL OR projectTitle LIKE ? ) ORDER BY "
							+ (queryBean.getOrderBy() + 1) + " " + (queryBean.getDesc() != 0 ? "DESC" : "")
							+ " LIMIT ?,?;");
			psCount = conn.prepareStatement(
					"SELECT COUNT(projectId) FROM projects WHERE (?=0 OR projectColumnId=?) AND (? IS NULL OR projectTitle LIKE ? )");
			psCount.setInt(1, queryBean.getColumnId());
			psCount.setInt(2, queryBean.getColumnId());
			psCount.setString(3, queryBean.getStr());
			psCount.setString(4, "%" + queryBean.getStr() + "%");
			resultSetCount = psCount.executeQuery();
			resultSetCount.next();
			sum = resultSetCount.getInt(1);
			if (sum > 0) {
				psProjects.setInt(1, queryBean.getColumnId());
				psProjects.setInt(2, queryBean.getColumnId());
				psProjects.setString(3, queryBean.getStr());
				psProjects.setString(4, "%" + queryBean.getStr() + "%");
				psProjects.setInt(5, page * size);
				psProjects.setInt(6, size);
				resultSet = psProjects.executeQuery();
				while (resultSet.next()) {
					ProjectBean project = new ProjectBean();
					project = new ProjectBean();
					set_project(project, resultSet);
					list.add(project);
				}
			}
		} finally {
			if (psProjects != null) {
				psProjects.close();
			}
			if (psCount != null) {
				psCount.close();
			}
			if (resultSetCount != null) {
				resultSetCount.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
		}
		return sum;
	}

	/**
	 * 插入App
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param projectId App基于的Project的ID
	 * @param userId 创建App的用户ID
	 * @param name App的名字
	 * @return 创建的App的ID
	 * @throws Exception 数据错误
	 */
	public final static int insert_application(Connection conn, int projectId, int userId, String name)
			throws Exception {
		PreparedStatement psInsert = null;
		ResultSet resultSet = null;
		int id = 0;
		try {
			psInsert = conn.prepareStatement("INSERT INTO apps(appProjectId,appUserId,appName) VALUES (?,?,?);",
					Statement.RETURN_GENERATED_KEYS);
			psInsert.setInt(1, projectId);
			psInsert.setInt(2, userId);
			psInsert.setString(3, name);
			if (psInsert.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
			resultSet = psInsert.getGeneratedKeys();
			resultSet.next();
			id = resultSet.getInt(1);
		} finally {
			if (psInsert != null) {
				psInsert.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
		}
		return id;
	}

	/**
	 * 查询App详细信息
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 查询的App的ID
	 * @return 查询出的App对象
	 * @throws Exception 数据错误
	 */
	public final static ApplicationBean select_application(Connection conn, int id) throws Exception {
		ApplicationBean app = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = conn.prepareStatement(
					"SELECT * FROM apps LEFT OUTER JOIN projects ON appProjectId=projectId LEFT OUTER JOIN users ON appUserId=userId WHERE appId=?;");
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				app = new ApplicationBean();
				set_app(app, resultSet);
				UserBean user = new UserBean();
				set_user(user, resultSet);
				ProjectBean project = new ProjectBean();
				set_project(project, resultSet);
				app.setProject(project);
				app.setUser(user);
			}
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return app;
	}

	/**
	 * 条件查询Apps
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param apps 查询出的App容器
	 * @param userid 通过用户查询的用户ID
	 * @param projectId 通过项目查询的项目ID
	 * @param page 页数
	 * @param size 每页数量
	 * @return 通过条件返回的总记录数
	 * @throws Exception 数据错误
	 */
	public final static int select_applications(Connection conn, List<ApplicationBean> apps, int userid, int projectId,
			int page, int size) throws Exception {
		PreparedStatement psCount = null;
		ResultSet rsCount = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			psCount = conn.prepareStatement(
					"SELECT count(appId) FROM apps WHERE (appUserId=? OR ?=0) AND (appProjectId=? OR ?=0);");
			psCount.setInt(1, userid);
			psCount.setInt(2, userid);
			psCount.setInt(3, projectId);
			psCount.setInt(4, projectId);

			rsCount = psCount.executeQuery();
			rsCount.next();
			count = rsCount.getInt(1);
			if (count > 0) {
				preparedStatement = conn.prepareStatement(
						"SELECT * FROM apps LEFT OUTER JOIN projects ON appProjectId=projectId LEFT OUTER JOIN users ON appUserId=userId  WHERE (appUserId=? OR ?=0) AND (appProjectId=? OR ?=0) LIMIT ?,?;");
				preparedStatement.setInt(1, userid);
				preparedStatement.setInt(2, userid);
				preparedStatement.setInt(3, projectId);
				preparedStatement.setInt(4, projectId);
				preparedStatement.setInt(5, size * page);
				preparedStatement.setInt(6, size);
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					ApplicationBean app = new ApplicationBean();
					app = new ApplicationBean();
					set_app(app, resultSet);
					UserBean user = new UserBean();
					set_user(user, resultSet);
					ProjectBean project = new ProjectBean();
					set_project(project, resultSet);
					app.setProject(project);
					app.setUser(user);
					apps.add(app);
				}
			}
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return count;
	}

	/**
	 * 删除APP
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 要删除的App的ID
	 * @throws Exception 数据错误
	 */
	public final static void delete_application(Connection conn, int id) throws Exception {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement("DELETE FROM apps WHERE appId=?;");
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}
	/**
	 * 更新Application的信息
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param id 要更新信息的App的ID
	 * @param name 新的App名字
	 * @param context 新的App内容
	 * @param data 新的App数据
	 * @throws Exception 数据错误
	 */
	public final static void update_application_info(Connection conn, int id, String name, String context, String data)
			throws Exception {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn
					.prepareStatement("UPDATE apps SET appName=?,appContext=?,appData=? WHERE appId=?;");
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, context);
			preparedStatement.setString(3, data);
			preparedStatement.setInt(4, id);
			if (preparedStatement.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}
	
	/**
	 * 插入评论
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param projectId 评论的项目iD
	 * @param appId 评论的App的ID
	 * @param userId 评论用户的ID
	 * @param context 评论内容
	 * @throws Exception 数据错误
	 */
	public final static void insert_commentary(Connection conn , int projectId , int appId , int userId, String context) throws Exception{
		PreparedStatement psInsert = null;
		try {
			psInsert = conn.prepareStatement("INSERT INTO commentarys(commentaryProjectId,commentaryAppId,commentaryUserId,commentaryContext) VALUES (?,?,?,?);");
			psInsert.setInt(1, projectId);
			psInsert.setInt(2, appId);
			psInsert.setInt(3, userId);
			psInsert.setString(4, context);
			if (psInsert.executeUpdate() != 1) {
				throw new Exception("Update rows number not is one!");
			}
		} finally {
			if (psInsert != null) {
				psInsert.close();
			}
		}
	}
	
	/**
	 * 条件查询评论
	 * @param conn 数据库连接 {@link CommonConfig} 类中getConn函数获得
	 * @param commentarys 评论的容器
	 * @param projectId 项目ID
	 * @param appId App的ID
	 * @param page 页数
	 * @param size 每页大小
	 * @return 条件查询返回总记录数
	 * @throws Exception 数据错误
	 */
	public final static int select_commentarys(Connection conn , List<CommentaryBean> commentarys, int projectId , int appId , int page , int size) throws Exception{
		PreparedStatement psCount = null;
		ResultSet rsCount = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			psCount = conn.prepareStatement(
					"SELECT count(commentaryId) FROM commentarys WHERE (commentaryAppId=? OR ?=0) AND (commentaryProjectId=? OR ?=0);");
			psCount.setInt(1, appId);
			psCount.setInt(2, appId);
			psCount.setInt(3, projectId);
			psCount.setInt(4, projectId);

			rsCount = psCount.executeQuery();
			rsCount.next();
			count = rsCount.getInt(1);
			if (count > 0) {
				preparedStatement = conn.prepareStatement(
						"SELECT * FROM commentarys LEFT OUTER JOIN users ON commentaryUserId=userId  WHERE (commentaryAppId=? OR ?=0) AND (commentaryProjectId=? OR ?=0) LIMIT ?,?;");
				preparedStatement.setInt(1, appId);
				preparedStatement.setInt(2, appId);
				preparedStatement.setInt(3, projectId);
				preparedStatement.setInt(4, projectId);
				preparedStatement.setInt(5, size * page);
				preparedStatement.setInt(6, size);
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					CommentaryBean commentary = new CommentaryBean();
					set_commentary(commentary, resultSet);
					UserBean user = new UserBean();
					set_user(user, resultSet);
					commentary.setUser(user);
					commentarys.add(commentary);
				}
			}
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
		return count;
	}
	
}
